<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>本地SQLite时间统计图表</title>
    <script src="https://cdn.tailwindcss.com"></script>
    <link href="https://cdn.jsdelivr.net/npm/font-awesome@4.7.0/css/font-awesome.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.8/dist/chart.umd.min.js"></script>
    <script>
        tailwind.config = {
            theme: {
                extend: {
                    colors: {
                        primary: '#3B82F6',
                        secondary: '#10B981',
                        accent: '#F59E0B',
                    },
                    fontFamily: {
                        sans: ['Inter', 'system-ui', 'sans-serif'],
                    },
                },
            }
        }
    </script>
    <style type="text/tailwindcss">
        @layer utilities {
            .content-auto {
                content-visibility: auto;
            }
            .input-shadow {
                box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1), 0 2px 4px -1px rgba(0, 0, 0, 0.06);
            }
            .chart-container {
                position: relative;
                height: 400px;
                width: 100%;
            }
            .file-drop {
                border: 2px dashed #ccc;
                transition: border-color 0.3s;
            }
            .file-drop.active {
                border-color: #3B82F6;
            }
        }
    </style>
</head>
<body class="bg-gray-50 min-h-screen">
<div class="container mx-auto px-4 py-8 max-w-6xl">
    <h1 class="text-3xl font-bold text-gray-800 mb-6 flex items-center">
        <i class="fa fa-database mr-3 text-primary"></i>本地SQLite时间统计图表
    </h1>

    <!-- 状态提示区 -->
    <div id="statusMessage" class="bg-blue-50 border-l-4 border-primary p-4 mb-8 hidden">
        <h3 class="font-semibold text-gray-800 flex items-center">
            <i class="fa fa-info-circle mr-2"></i>请上传本地SQLite文件（.db）
        </h3>
        <p class="text-gray-600 text-sm mt-1">支持查询时间统计数据并生成图表。</p>
    </div>

    <!-- 本地SQLite文件上传区 -->
    <div class="bg-white rounded-lg shadow-md p-6 mb-8">
        <h2 class="text-xl font-semibold text-gray-700 mb-4">上传本地SQLite文件</h2>
        <div id="fileDropArea" class="file-drop p-8 text-center rounded-md mb-4">
            <i class="fa fa-cloud-upload text-4xl text-gray-400 mb-2"></i>
            <p class="text-gray-600">拖放SQLite文件（.db）到此处，或点击选择文件</p>
            <input type="file" id="sqliteFileInput" accept=".db" class="hidden" />
            <button id="browseFileBtn" class="mt-4 bg-gray-100 hover:bg-gray-200 text-gray-700 font-medium py-2 px-4 rounded-md transition duration-300">
                <i class="fa fa-folder-open mr-2"></i>选择文件
            </button>
        </div>
        <div id="fileInfo" class="text-sm text-gray-600 hidden">
            <p><i class="fa fa-file-text-o mr-1"></i>已选择文件：<span id="fileName">-</span></p>
        </div>
    </div>

    <!-- SQL查询输入区 -->
    <div class="bg-white rounded-lg shadow-md p-6 mb-8">
        <h2 class="text-xl font-semibold text-gray-700 mb-4">SQL查询输入</h2>
        <textarea id="sqlInput" class="w-full h-24 p-3 border border-gray-300 rounded-md input-shadow focus:ring-2 focus:ring-primary/50 focus:border-primary transition duration-300" placeholder="请输入SQL查询（例如：SELECT category, details, SUM(duration_seconds) AS total FROM time_records GROUP BY category, details）" disabled>
SELECT category, details, SUM(duration_seconds) AS total
FROM time_records
GROUP BY category, details
      </textarea>
        <button id="executeSql" class="mt-4 bg-primary hover:bg-primary/90 text-white font-medium py-2 px-4 rounded-md transition duration-300 flex items-center disabled:opacity-50" disabled>
            <i class="fa fa-play mr-2"></i>执行SQL并生成图表
        </button>
    </div>

    <!-- 图表展示区 -->
    <div class="grid grid-cols-1 lg:grid-cols-2 gap-8">
        <div class="bg-white rounded-lg shadow-md p-6">
            <h2 class="text-xl font-semibold text-gray-700 mb-4">时间分布饼状图（占比）</h2>
            <div class="chart-container">
                <canvas id="timePieChart"></canvas>
            </div>
        </div>
        <div class="bg-white rounded-lg shadow-md p-6">
            <h2 class="text-xl font-semibold text-gray-700 mb-4">时间分布柱状图（时长）</h2>
            <div class="chart-container">
                <canvas id="timeBarChart"></canvas>
            </div>
        </div>
    </div>

    <!-- 加载SQLite WASM -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/sql-wasm.js"></script>
    <script>
        document.addEventListener('DOMContentLoaded', async function() {
            // DOM元素
            const fileDropArea = document.getElementById('fileDropArea');
            const sqliteFileInput = document.getElementById('sqliteFileInput');
            const browseFileBtn = document.getElementById('browseFileBtn');
            const fileInfo = document.getElementById('fileInfo');
            const fileName = document.getElementById('fileName');
            const sqlInput = document.getElementById('sqlInput');
            const executeSqlBtn = document.getElementById('executeSql');
            const statusMessage = document.getElementById('statusMessage');
            const pieCanvas = document.getElementById('timePieChart');
            const barCanvas = document.getElementById('timeBarChart');

            // 变量
            let db = null; // SQLite数据库实例
            let pieChart = null;
            let barChart = null;
            const colors = [
                '#3B82F6', '#10B981', '#F59E0B', '#EF4444',
                '#8B5CF6', '#06B6D4', '#EC4899', '#6B7280'
            ];

            // 初始化SQL.js
            let SQL = null;
            try {
                SQL = await initSqlJs({
                    locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/${file}`
            });
                statusMessage.classList.remove('hidden');
            } catch (error) {
                statusMessage.innerHTML = `
            <h3 class="font-semibold text-red-800 flex items-center">
              <i class="fa fa-exclamation-circle mr-2"></i>SQLite初始化失败
            </h3>
            <p class="text-red-600 text-sm mt-1">${error.message}</p>
          `;
                return;
            }

            // 文件上传相关事件
            browseFileBtn.addEventListener('click', () => sqliteFileInput.click());
            sqliteFileInput.addEventListener('change', handleFileSelect);

            // 拖放事件
            fileDropArea.addEventListener('dragover', (e) => {
                e.preventDefault();
            fileDropArea.classList.add('active');
        });
            fileDropArea.addEventListener('dragleave', () => {
                fileDropArea.classList.remove('active');
        });
            fileDropArea.addEventListener('drop', (e) => {
                e.preventDefault();
            fileDropArea.classList.remove('active');
            if (e.dataTransfer.files.length) {
                handleFile(e.dataTransfer.files[0]);
            }
        });

            // 处理文件选择
            function handleFileSelect(e) {
                if (e.target.files.length) {
                    handleFile(e.target.files[0]);
                }
            }

            // 加载并解析SQLite文件
            function handleFile(file) {
                if (!file.name.endsWith('.db')) {
                    alert('请上传SQLite数据库文件（.db格式）');
                    return;
                }

                // 显示文件信息
                fileName.textContent = file.name;
                fileInfo.classList.remove('hidden');
                statusMessage.innerHTML = `
            <h3 class="font-semibold text-gray-800 flex items-center">
              <i class="fa fa-spinner fa-spin mr-2"></i>正在加载数据库...
            </h3>
            <p class="text-gray-600 text-sm mt-1">文件大小：${(file.size / 1024).toFixed(1)}KB</p>
          `;

                // 读取文件并初始化数据库
                const reader = new FileReader();
                reader.onload = (e) => {
                    try {
                        const uInt8Array = new Uint8Array(e.target.result);
                        db = new SQL.Database(uInt8Array); // 加载本地数据库
                        statusMessage.innerHTML = `
                <h3 class="font-semibold text-green-800 flex items-center">
                  <i class="fa fa-check-circle mr-2"></i>数据库加载成功
                </h3>
                <p class="text-green-600 text-sm mt-1">可以执行SQL查询了</p>
              `;
                        // 启用SQL输入和执行按钮
                        sqlInput.disabled = false;
                        executeSqlBtn.disabled = false;
                    } catch (error) {
                        statusMessage.innerHTML = `
                <h3 class="font-semibold text-red-800 flex items-center">
                  <i class="fa fa-exclamation-circle mr-2"></i>数据库解析失败
                </h3>
                <p class="text-red-600 text-sm mt-1">${error.message}</p>
              `;
                    }
                };
                reader.readAsArrayBuffer(file);
            }

            // 执行SQL并生成图表
            executeSqlBtn.addEventListener('click', () => {
                if (!db) {
                alert('请先上传并加载SQLite数据库');
                return;
            }

            const sql = sqlInput.value.trim();
            if (!sql) {
                alert('请输入SQL查询语句');
                return;
            }

            try {
                // 执行SQL查询
                const result = db.exec(sql);
                if (!result || result.length === 0 || !result[0].values || result[0].values.length === 0) {
                    alert('查询无结果，请检查SQL语句或表结构');
                    return;
                }

                // 转换查询结果为图表所需格式（需确保查询返回category, details, total三个字段）
                const data = result[0].values.map(row => ({
                    category: row[0],
                    details: row[1],
                    total: row[2]
                }));

                // 渲染图表
                renderCharts(data);
            } catch (error) {
                alert(`SQL执行错误：${error.message}`);
            }
        });

            // 渲染图表
            function renderCharts(data) {
                const labels = data.map(item => `${item.category}-${item.details}`);
                const values = data.map(item => item.total);

                // 饼图
                if (pieChart) pieChart.destroy();
                pieChart = new Chart(pieCanvas, {
                    type: 'pie',
                    data: {
                        labels: labels,
                        datasets: [{
                            data: values,
                            backgroundColor: colors,
                            borderWidth: 1
                        }]
                    },
                    options: {
                        responsive: true,
                        maintainAspectRatio: false,
                        plugins: {
                            legend: { position: 'right' },
                            tooltip: {
                                callbacks: { label: (ctx) => formatTooltip(ctx, values) }
                        }
                    }
                }
            });

                // 柱状图
                if (barChart) barChart.destroy();
                barChart = new Chart(barCanvas, {
                    type: 'bar',
                    data: {
                        labels: labels,
                        datasets: [{
                            label: '时长（秒）',
                            data: values,
                            backgroundColor: colors,
                            borderRadius: 4
                        }]
                    },
                    options: {
                        responsive: true,
                        maintainAspectRatio: false,
                        scales: {
                            y: { beginAtZero: true, title: { display: true, text: '时长（秒）' } },
                            x: { ticks: { maxRotation: 45, minRotation: 45 } }
                        },
                        plugins: {
                            tooltip: {
                                callbacks: { label: (ctx) => formatTooltip(ctx, values) }
                        }
                    }
                }
            });
            }

            // 格式化提示信息（时分秒+百分比）
            function formatTooltip(context, allValues) {
                const label = context.label || '';
                const value = context.raw;
                const total = allValues.reduce((a, b) => a + b, 0);
                const percentage = ((value / total) * 100).toFixed(1);
                const hours = Math.floor(value / 3600);
                const minutes = Math.floor((value % 3600) / 60);
                const seconds = value % 60;
                return `${label}: ${hours}小时${minutes}分钟${seconds}秒 (${percentage}%)`;
            }
        });
    </script>
</div>
</body>
</html>